package com.example.jspwork.Dao;


import com.example.jspwork.pojo.InformBean;
import com.example.jspwork.pojo.ShowIndex;
import com.example.jspwork.pojo.nowBean;
import com.example.jspwork.pojo.queryDress;
import com.example.jspwork.utils.JDBCUtils;
import com.example.jspwork.utils.MyUtil;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.time.LocalDateTime;
import java.util.ArrayList;
import java.util.List;

public class AddressQuery {
    public static void main(String[] args) throws SQLException {
//        queryDress("北京");
        queryManage();

    }

    /**
     * @param dress
     *
     * &#064;describe  输入一个城市地址，返回一个城市的天气预报信息对象数组
     * @return ShowIndex_list
     */
    public static List<ShowIndex> queryDress(String dress) {

        Number id=0;
        List<ShowIndex> ShowIndex_list = new ArrayList<>();
        String sql ="SELECT weather.id, weather.name, weather.city, weather.province, weather.country,\n" +
                "       now.text, now.temp, now.feels_like, now.rh, now.wind_class, now.wind_dir, now.uptime\n" +
                "FROM weather  INNER JOIN now ON weather.id = now.id  WHERE weather.name = ? ;";

        try(Connection conn =JDBCUtils.getConnection()){
            PreparedStatement pstmt =conn.prepareStatement(sql);
            pstmt.setString(1,dress);
            ResultSet rs = pstmt.executeQuery();
//            System.out.println(rs);



            while (rs.next()) {
                ShowIndex_list.add(new ShowIndex(
                        rs.getInt("id"),
                        rs.getString("name"),
                        rs.getString("city"),
                        rs.getString("province"),
                        rs.getString("country"),
                        rs.getString("text"),
                        rs.getInt("temp"),
                        rs.getInt("feels_like"),
                        rs.getInt("rh"),
                        rs.getString("wind_class"),
                        rs.getString("wind_dir"),
                        rs.getString("uptime"))
                         );
            }
            System.out.println(ShowIndex_list);

            rs.close();
            pstmt.close();



        }catch (SQLException e) {
            e.printStackTrace();
        }
        return ShowIndex_list;

    }
//    数据库更新

    /**
     *
     * @param info
     * &#064;describe 数据库更新不过只有6个字段。参数info为一个对象，前端axios封装的一个对象
     * @throws SQLException
     */
    public  static void update(InformBean info) throws SQLException{

        String sql1 ="update now set weather=?, wind_dir=?, wind_class=?, " +
                "temp=?, update_time=? where id=?";


        try(Connection conn = JDBCUtils.getConnection()){
            PreparedStatement pstmt =conn.prepareStatement(sql1);
            pstmt.setString(1,info.getWeather() );
            pstmt.setString(2,info.getWind_dir() );
            pstmt.setString(3,info.getWind_class() );
            pstmt.setInt(4,info.getTemp() );
            pstmt.setString(5,info.getUptime().isEmpty() ?
                    MyUtil.DTStr(LocalDateTime.now()) : info.getUptime());
            pstmt.setInt(6,info.getId());


            int n = pstmt.executeUpdate();
            System.out.println("更新数据库影响了: " + n + " 行");

            pstmt.close();

            if(n==0){
                pstmt = conn.prepareStatement("insert into now (weather, wind_dir, wind_class, temp, city, update_time, id)" +
                        " values (?,?,?,?,?,?,?)");
                pstmt.setString(1, info.getWeather()); // 第一个参数 weather=?
                pstmt.setString(2, info.getWind_dir()); // 第二个参数 wind_dir=?
                pstmt.setString(3, info.getWind_class()); // 第三个参数 wind_class=?
                pstmt.setInt(4, info.getTemp()); // 第四个参数 temp=?
                pstmt.setString(5, info.getCity()); // 第五个参数 city=?
                pstmt.setString(6, info.getUptime().isEmpty() ?
                        MyUtil.DTStr(LocalDateTime.now()) : info.getUptime()); // 第六个参数 update_time=?
                pstmt.setInt(7, info.getId()); // 第七个参数 id=?

                //第三步 执行SQL语句
                n = pstmt.executeUpdate();
                System.out.println("插入数据库影响了: " + n + " 行");
                // 第四步 关闭释放资源
                pstmt.close();


            }

        }
    }

//    数据库管理天气预报

    /**
     *
     * @return Manage_list
     * &#064;describe 这个跟老师的一样，因为我数据库不一样，所以改进sql语句了
     *
     */
    public static List<nowBean> queryManage() {


    List<nowBean> Manage_list = new ArrayList<>();
    String sql2  = "SELECT now.id, weather.name, now.text,now.temp, now.feels_like,now.rh,now.wind_class, now.wind_dir,  now.uptime FROM now LEFT JOIN weather ON now.id = weather.id  LIMIT 20 ";

    try(Connection conn =JDBCUtils.getConnection()){
        PreparedStatement pstmt =conn.prepareStatement(sql2);

        ResultSet rs = pstmt.executeQuery();
            System.out.println(rs);



        while (rs.next()) {
            Manage_list.add(new nowBean(
                    rs.getInt("id"),
                    rs.getString("name"),
                    rs.getString("text"),
                    rs.getInt("temp"),
                    rs.getInt("feels_like"),
                    rs.getInt("rh"),
                    rs.getString("wind_class"),
                    rs.getString("wind_dir"),
                    rs.getString("uptime"))
            );
        }
        System.out.println(Manage_list);

        rs.close();
        pstmt.close();



    }catch (SQLException e) {
        e.printStackTrace();
    }
    return Manage_list;

}




}
